package com.pervasive.geo_tag.servlets;

import java.io.IOException;
import java.io.PrintWriter;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;




import com.pervasive.geo_tag.server.Note;
import com.pervasive.geo_tag.server.Photo;

/**
 * Servlet implementation class LoadServlet
 */
@WebServlet("/LoadServlet")
public class LoadServlet extends HttpServlet implements Serializable {
	
	private static final long serialVersionUID = 1L;
	private String location;
	private ArrayList<Photo> listOfphotos = new ArrayList<Photo>();
	private ArrayList<Note> listOfnotes = new ArrayList<Note>();
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public LoadServlet() {
        super();
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
	
		location = request.getParameter("location").replace(" ", "+");
		System.out.println(location);
		String dbName = "android_geo_tag";
	    String query;
		PrintWriter out = response.getWriter();
		Statement stmt = null;
	    Connection dbConn = null; 
	    ResultSet rs = null;
	    
	    try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException e) {e.printStackTrace();
		} catch (IllegalAccessException e) {e.printStackTrace();
		} catch (ClassNotFoundException e) {e.printStackTrace();}
	    try {
			dbConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/android_geo_tag", "root", "seppiessa");
		} catch (SQLException e) {e.printStackTrace();}
	   
		try {
			stmt = dbConn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
		} catch (SQLException e) {e.printStackTrace();}
	    
		query = "select * from " + dbName + ".photo where location = '" + location + "'";
		
	    try {
			rs = stmt.executeQuery(query);
		} catch (SQLException e1) {e1.printStackTrace();}
			
		try {
				
/*Loading photos into the ArrayList of photos taken from MYSQL DB */
			
				while(rs.next()){
					Photo f = new Photo();
					f.setId_photo(rs.getInt("ID_PHOTO"));
					f.setUri_photo(rs.getString("URI"));
					f.setData(rs.getString("DATA"));
					f.setSize(rs.getLong("SIZE"));
					f.setId_note(rs.getInt("ID_NOTE"));
					f.setLocation(rs.getString("LOCATION"));
					listOfphotos.add(f);
				}
			} catch (SQLException e) {e.printStackTrace();}
			
/*Loading notes into the ArrayList of notes taken from MYSQL DB */
			
		for(int i = 0; i < listOfphotos.size(); i++){
				
					query = "select * from " +dbName +".note where id_note = " + listOfphotos.get(i).getId_note();
					try {
						rs = stmt.executeQuery(query);
					} catch (SQLException e) {e.printStackTrace();}
					
					try {
						while(rs.next()){
							Note n = new Note();
							n.setId_note(rs.getInt("ID_NOTE"));
							n.setText(rs.getString("TEXT"));
							listOfnotes.add(n);
						}
					} catch (SQLException e) {e.printStackTrace();}
					
			}
			
		response.setHeader("Content-type", "text/xml");
		response.setHeader("Cache-control", "no-cache");
		out.println("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
		out.println("<loadInfo xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:noNamespaceSchemaLocation=\"geotagschema.xsd\">");
		out.println("<listOfPhotos>");
				
				for(int i = 0; i < listOfphotos.size(); i++){
						out.println("<photo>" +
									"<id_photo>" + listOfphotos.get(i).getId_photo() + "</id_photo>" + 
									"<id_note>" + listOfphotos.get(i).getId_note() + "</id_note>"+
									"<uri>" + listOfphotos.get(i).getUri_photo() + "</uri>" +
									"<size>" +  listOfphotos.get(i).getSize() + "</size>" + 
									"<data>" + listOfphotos.get(i).getData() + "</data></photo>");
						
					}
				
		out.println("</listOfPhotos>");
		out.println("<listOfNotes xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:noNamespaceSchemaLocation=\"geotagschema.xsd\">");

				for(int i = 0; i < listOfnotes.size(); i++){
						out.println("<note>" + 
									"<id_note>" + listOfnotes.get(i).getId_note() + "</id_note>" +
									"<text>" + listOfnotes.get(i).getText() + "</text>" +
									"</note>");
				}
				
		out.println("</listOfNotes>");
		out.println("</loadInfo>");
		out.close();
		listOfphotos.clear();
		listOfnotes.clear();

	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
	}

}
